package com.scs.application.modules.upms.service.impl;

import com.alibaba.fastjson.JSONArray;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.google.common.collect.Lists;
import com.scs.application.consts.GlobalConsts;
import com.scs.application.core.service.BaseServiceImpl;
import com.scs.application.core.utils.Exceptions;
import com.scs.application.core.utils.StringUtils;
import com.scs.application.modules.upms.entity.GenDatasource;
import com.scs.application.modules.upms.entity.GenTable;
import com.scs.application.modules.upms.entity.GenTableColumn;
import com.scs.application.modules.upms.gendir.GenUtils;
import com.scs.application.modules.upms.mapper.GenTableMapper;
import com.scs.application.modules.upms.service.GenDatasourceService;
import com.scs.application.modules.upms.service.GenTableColumnService;
import com.scs.application.modules.upms.service.GenTableService;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import javax.swing.filechooser.FileSystemView;
import java.io.IOException;
import java.io.Serializable;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;

/**
 * @Description:DictTypeServiceImpl
 **/
@Slf4j
@Service
public class GenTableServiceImpl extends BaseServiceImpl<GenTableMapper, GenTable> implements GenTableService {

    @Autowired
    private GenDatasourceService genDatasourceService;

    @Autowired
    private GenTableColumnService genTableColumnService;

    // jdbc template 缓存
//    private final ConcurrentHashMap<String, JdbcTemplate> jdbcTemplateMap = new ConcurrentHashMap<>();


    // 查询数据库表格SQL
    private static final String FIND_TABLES_SQL = "SELECT t.table_name,t.TABLE_COMMENT AS comments " +
            "            FROM information_schema.`TABLES` t " +
            "            WHERE t.TABLE_SCHEMA = (select database()) " +
            "            ORDER BY t.TABLE_NAME";


    private static final String FIND_COLUMNS_SQL = "SELECT t.COLUMN_NAME, (CASE WHEN t.IS_NULLABLE = 'YES' THEN '1' ELSE '0' END) AS isNull,(CASE WHEN t.column_key = 'PRI' THEN '1' ELSE '0' END) AS isPk," +
            "            (t.ORDINAL_POSITION * 10) AS column_sort,t.COLUMN_COMMENT AS comments,t.COLUMN_TYPE AS columnType" +
            "            FROM information_schema.`COLUMNS` t" +
            "            WHERE t.TABLE_SCHEMA = (select database())" +
            "                AND t.TABLE_NAME = upper(?)" +
            "            ORDER BY t.ORDINAL_POSITION";


    @Override
    public List<GenTable> findTableListFromDB(String datasourceId) {
        if (StringUtils.isBlank(datasourceId)) {
            return Collections.emptyList();
        }

        GenDatasource genDatasource = genDatasourceService.getById(datasourceId);
        if (genDatasource == null) {
            return Collections.emptyList();
        }

        DataSource dataSource = genDatasourceService.getRealDatasource(genDatasource.getId());

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        List<GenTable> tableList = jdbcTemplate.query(FIND_TABLES_SQL, new GenTableRowMapper());

        for (GenTable genTable : tableList) {
            if (StringUtils.isBlank(genTable.getClassName())) {
                genTable.setClassName(StringUtils.capCamelCase(genTable.getTableName()));
            }
            if (StringUtils.isBlank(genTable.getPackageName())) {
                genTable.setPackageName("fortec.common");
            }
            if (StringUtils.isBlank(genTable.getGenBaseDir())) {
                genTable.setGenBaseDir(FileSystemView.getFileSystemView().getHomeDirectory().getAbsolutePath());
            }
        }

        return tableList;
    }

    @Override
    public List<GenTableColumn> findTableColumnListFromDB(String datasourceId, String tableName) {
        GenDatasource genDatasource = genDatasourceService.getById(datasourceId);
        if (genDatasource == null) {
            return Collections.emptyList();
        }

        DataSource dataSource = genDatasourceService.getRealDatasource(genDatasource.getId());

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        List<GenTableColumn> columns = jdbcTemplate.query(FIND_COLUMNS_SQL, new Object[]{tableName}, new GenTableColumnRowMapper());

        for (GenTableColumn column : columns) {
            initColumn(column);
        }

        return columns;
    }

    @Transactional
    @Override
    public void saveOrUpdateGenInfo(GenTable genTable) {
        this.saveOrUpdate(genTable);

        List<GenTableColumn> columns = genTable.getColumns();
        for (GenTableColumn column : columns) {
            column.setTableId(genTable.getId());
            column.setTableName(genTable.getTableName());
        }

        genTableColumnService.saveOrUpdateBatch(columns);
    }

    @SneakyThrows
    @Transactional
    @Override
    public List<String> saveAndGenerate(GenTable genTable) {
        saveOrUpdateGenInfo(genTable);

        // 主子表
        List<GenTable> childTableList = this.baseMapper.selectList(Wrappers.<GenTable>query().eq("parent_table_name", genTable.getTableName()).eq("datasource_id", genTable.getDatasourceId()));
        genTable.setChildTableList(childTableList);

        List<String> result = Lists.newArrayList();
        if (CollectionUtils.isNotEmpty(childTableList)) {
            for (GenTable table : childTableList) {
                table.setColumns(genTableColumnService.list(Wrappers.<GenTableColumn>query()
                        .eq("table_id", table.getId()).orderByAsc("column_sort")));
                result.addAll(GenUtils.generateToFile(table, true));
            }
            genTable.setChildTableList(childTableList);
        }
        try {
            result.addAll(GenUtils.generateToFile(genTable, true));
            return result;
        } catch (IOException e) {
            throw Exceptions.unchecked(e);
        }
    }

    @Override
    public boolean removeCascadeById(Serializable id) {
        genTableColumnService.remove(Wrappers.<GenTableColumn>query().eq("table_id", id));

        return this.removeById(id);
    }


    private void initColumn(GenTableColumn column) {
        // 设置Java属性名
        if (StringUtils.isNotBlank(column.getColumnName())) {
            column.setAttrName(StringUtils.camelCase(column.getColumnName()));
        }

        column.setIsList(column.getIsList() == null ? GlobalConsts.NO : column.getIsList());
        // 设置Java字段属性类型
        String columnType = column.getColumnType();
        if (StringUtils.startsWithIgnoreCase(columnType, "CHAR")
                || StringUtils.startsWithIgnoreCase(columnType, "VARCHAR")
                || StringUtils.startsWithIgnoreCase(columnType, "NARCHAR")
                || StringUtils.startsWithIgnoreCase(columnType, "TEXT")) {
            column.setAttrType("String");
            column.setShowType("input");
        } else if (StringUtils.startsWithIgnoreCase(columnType, "DATETIME")
                || StringUtils.startsWithIgnoreCase(columnType, "DATE")
                || StringUtils.startsWithIgnoreCase(columnType, "TIMESTAMP")) {
            column.setAttrType("java.util.Date");
            column.setShowType("datetime");
        } else if (StringUtils.startsWithIgnoreCase(columnType, "BIGINT")
                || StringUtils.startsWithIgnoreCase(columnType, "TINYINT")
                || StringUtils.startsWithIgnoreCase(columnType, "INT")
                || StringUtils.startsWithIgnoreCase(columnType, "DECIMAL")
                || StringUtils.startsWithIgnoreCase(columnType, "NUMBER")
                || StringUtils.startsWithIgnoreCase(columnType, "smallint")) {
            // 如果是浮点型
            String[] ss = StringUtils.split(StringUtils.substringBetween(columnType, "(", ")"), ",");
            if (ss != null && ss.length == 2 && Integer.parseInt(ss[1]) > 0) {
                column.setAttrType("Double");
            }
            // 如果是整形
            else if (ss != null && ss.length == 1 && Integer.parseInt(ss[0]) <= 10) {
                column.setAttrType("Integer");
            }
            // 长整形
            else {
                column.setAttrType("Long");
            }
            column.setShowType("input");
        } else if (StringUtils.startsWithIgnoreCase(columnType, "ENUM")) {
            column.setAttrType("String");
            column.setShowType("select");
        } else if (StringUtils.startsWithIgnoreCase(columnType, "JSON")) {
            column.setAttrType("String");
            column.setShowType("input");
        }

        // 是否是主键
        column.setIsPk(column.getIsPk() == null ? GlobalConsts.NO : column.getIsPk());

        // 插入字段
        column.setIsInsert(GlobalConsts.YES);

        // 修改字段
        column.setIsUpdate(GlobalConsts.YES);

        // 编辑字段
        String columnName = column.getColumnName();
        if (!StringUtils.equalsIgnoreCase(columnName, "id")
                && !StringUtils.equalsIgnoreCase(columnName, "creator")
                && !StringUtils.equalsIgnoreCase(columnName, "gmt_create")
                && !StringUtils.equalsIgnoreCase(columnName, "modifier")
                && !StringUtils.equalsIgnoreCase(columnName, "gmt_modified")
                && !StringUtils.equalsIgnoreCase(columnName, "del_flag")) {
            column.setIsEdit(GlobalConsts.YES);
            column.setIsQuery(GlobalConsts.YES);
        } else {
            column.setIsEdit(GlobalConsts.NO);
            column.setIsQuery(GlobalConsts.NO);
        }
        column.setIsQuery(GlobalConsts.NO);

        // 列表字段
        if (StringUtils.equalsIgnoreCase(columnName, "name")
                || StringUtils.equalsIgnoreCase(columnName, "title")
                || StringUtils.equalsIgnoreCase(columnName, "remarks")
                || StringUtils.equalsIgnoreCase(columnName, "gmt_modified")) {
            column.setIsList(GlobalConsts.YES);
        }


        if ("id".equals(columnName)) {
            column.setShowType("hidden");
            column.setAttrType("String");
        }

        column.setShowType(StringUtils.defaultString(column.getShowType()));
        // 查询字段类型
        switch (column.getShowType()) {
            case "input":
            case "select":
            case "textarea":
                column.setQueryType("=");
                break;
            case "date":
            case "datetime":
                column.setQueryType("between");
                break;
            default:
                column.setQueryType("=");
                break;
        }

        // 设置栅格
        switch (column.getShowType()) {
            case "input":
            case "select":
            case "date":
            case "datetime":
                column.setIsNewRow(GlobalConsts.NO);
                column.setColSpan("6");
                break;
            case "textarea":
                column.setIsNewRow(GlobalConsts.YES);
                column.setColSpan("12");
                break;
            default:
                column.setIsNewRow(GlobalConsts.NO);
                column.setColSpan("6");
                break;
        }

        if (StringUtils.isNotBlank(column.getId()) || column.getIsPk() == GlobalConsts.YES) {
            return;
        }

        // 设置表单验证
        JSONArray validators = new JSONArray();

        if (column.getIsNull() == GlobalConsts.NO) {
            validators.add(0, "required");
        }
        if (StringUtils.startsWithIgnoreCase(columnType, "BIGINT")
                || StringUtils.startsWithIgnoreCase(columnType, "TINYINT")
                || StringUtils.startsWithIgnoreCase(columnType, "INT")
                || StringUtils.startsWithIgnoreCase(columnType, "DECIMAL")
                || StringUtils.startsWithIgnoreCase(columnType, "NUMBER")
                || StringUtils.startsWithIgnoreCase(columnType, "smallint")) {
            validators.add("number");
        }

    }


    private class GenTableRowMapper implements RowMapper<GenTable> {

        @Override
        public GenTable mapRow(ResultSet resultSet, int i) throws SQLException {
            GenTable table = new GenTable();
            table.setComments(resultSet.getString("comments"))
                    .setTableName(resultSet.getString("table_name"));

            return table;
        }
    }

    private class GenTableColumnRowMapper implements RowMapper<GenTableColumn> {

        @Override
        public GenTableColumn mapRow(ResultSet rs, int rowNum) throws SQLException {
            GenTableColumn column = new GenTableColumn();

//            SELECT t.COLUMN_NAME, (CASE WHEN t.IS_NULLABLE = 'YES' THEN '1' ELSE '0' END) AS isNull,(CASE WHEN t.column_key = 'PRI' THEN '1' ELSE '0' END) AS isPk," +
//            "            (t.ORDINAL_POSITION * 10) AS column_sort,t.COLUMN_COMMENT AS comments,t.COLUMN_TYPE AS columnType" +
//                    "            FROM information_schema.`COLUMNS` t" +
//                    "            WHERE t.TABLE_SCHEMA = (select database())" +
//                    "                AND t.TABLE_NAME = upper(?)" +
//                    "            ORDER BY t.ORDINAL_POSITION；;
            column.setColumnName(rs.getString("COLUMN_NAME"))
                    .setIsNull(rs.getShort("isNull"))
                    .setIsPk(rs.getShort("isPk"))
                    .setColumnSort(rs.getInt("column_sort"))
                    .setComments(rs.getString("comments"))
                    .setColumnType(rs.getString("columnType"))
            ;
            return column;
        }
    }

}
